/**
 * <pre>
 * Copyright (c) 2017 阿信sxq(songxinqiang@vip.qq.com).
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * </pre>
 */
/*
 * 创建时间：2017年5月31日--上午11:16:30
 * 作者：宋信强(阿信sxq, songxinqiang@vip.qq.com, https://my.oschina.net/songxinqiang)
 * <p>
 * 众里寻她千百度, 蓦然回首, 那人却在灯火阑珊处.
 * </p>
 */
package cn.songxinqiang.example.poi.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.songxinqiang.example.poi.model.User;

/**
 * poi处理xls文件
 *
 * @author 阿信sxq
 *
 */
public class PoiXlsService {

    private static final Logger logger = LoggerFactory.getLogger(PoiXlsService.class);

    /**
     * 将用户列表转换存入xls文件
     *
     * @param userList
     *            用户信息列表
     * @return 保存的临时文件的信息对象，用于后续操作
     */
    public File userListToFile(List<User> userList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet spreadsheet = workbook.createSheet("用户列表");

        // 用于第一行的样式，当做标题
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);
        font.setItalic(true);
        font.setBold(true);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);

        HSSFRow row;
        Cell cell;
        int rowid = 0;
        int cellid = 0;
        row = spreadsheet.createRow(rowid++);
        row.setRowStyle(style);
        row.setHeightInPoints(27);

        cell = row.createCell(cellid);
        cell.setCellValue("编号");
        spreadsheet.setColumnWidth(cellid, 18 * 256);
        cellid++;

        cell = row.createCell(cellid);
        cell.setCellValue("用户名");
        spreadsheet.setColumnWidth(cellid, 18 * 256);
        cellid++;

        for (User user : userList) {
            try {
                cellid = 0;
                row = spreadsheet.createRow(rowid++);

                cell = row.createCell(cellid++);
                cell.setCellValue(user.getId());
                cell = row.createCell(cellid++);
                cell.setCellValue(user.getUsername());
            } catch (Exception e) {
                logger.error("parse list error," + e.getMessage());
            }
        }
        // 文件只是临时文件，在方法返回之后，对文件进行后续的移动位置等操作
        File file = new File("/tmp/userlist.xls");
        try {
            FileOutputStream fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (Exception e) {
            logger.error("write file failed," + e.getMessage());
        } finally {
            try {
                workbook.close();
            } catch (IOException e1) {
                logger.error("close workbook error," + e1.getMessage());
            }
        }
        return file;
    }

    /**
     * 读取文件中的值得到记录列表
     *
     * @param file
     *            文件描述信息
     * @return 记录对象列表
     */
    public List<User> fileToList(File file) {
        List<User> userList = new ArrayList<>();
        HSSFWorkbook workbook = null;
        try {
            NPOIFSFileSystem poifs = new NPOIFSFileSystem(file);
            workbook = new HSSFWorkbook(poifs);
        } catch (Exception e) {
            logger.error("read file error:" + e.getMessage());
            return userList;
        }
        HSSFSheet spreadsheet = workbook.getSheetAt(0);
        int rowCount = spreadsheet.getLastRowNum();
        HSSFRow row;
        // 第一行忽略
        for (int rowId = 1; rowId <= rowCount; rowId++) {
            row = spreadsheet.getRow(rowId);

            User user = new User();

            // 这里获取“数字”类型的值得到的是double
            user.setId((int) row.getCell(0).getNumericCellValue());
            user.setUsername(row.getCell(1).getStringCellValue());

            userList.add(user);
        }
        try {
            workbook.close();
        } catch (IOException e) {}

        return userList;
    }
}
